

USE fat1_c1203i_s3_g2
go

DECLARE
		@ecardId int,
		@title varchar(100),
		@message_ecard varchar(1000),
		@signatures varchar(25),
		@Bool int
		set @Bool = 0;
if DAY(GETDATE()) = 25 and MONTH(GETDATE()) = 12
	BEGIN
		set @Bool = 1;
		-- lay random ra id cua card can gui
		SELECT TOP 1 @ecardId = ecardId FROM Ecard where typeEcardId = 8 ORDER BY NEWID()
				-- set value for available
				set @title ='Merry Christmas';
				set @message_ecard ='Merry Christmas to you';
				set @signatures = '';
	END
if DAY(GETDATE()) = 14 and MONTH(GETDATE()) = 2
	BEGIN
	   set @Bool = 1;
		-- lay random ra id cua card can gui
		SELECT TOP 1 @ecardId = ecardId FROM Ecard where typeEcardId = 6 ORDER BY NEWID()
				-- set value for available
				set @title ='Happy Valentine';
				set @message_ecard ='Happy Valentine To You';
				set @signatures = '';
	END
if DAY(GETDATE()) = 1 and MONTH(GETDATE()) = 1
	BEGIN
	    set @Bool = 1;
		-- lay random ra id cua card can gui
		SELECT TOP 1 @ecardId = ecardId FROM Ecard where typeEcardId = 3 ORDER BY NEWID()
				-- set value for available
				set @title ='Happy New Year';
				set @message_ecard ='Happy New Year To You';
				set @signatures = '';
	END
if @Bool = 1
BEGIN
	if object_id('customer_temp') is not null
		BEGIN
		  drop table customer_temp
		END
		SELECT * INTO customer_temp FROM customer WHERE status='subcrible'
		WHILE(SELECT COUNT(*) FROM customer_temp) > 0  
		BEGIN
			DECLARE
				@customerID int,
				@emailSender varchar(200),
				@message varchar(max),
				@subject_sent varchar(200),
				@Receiver varchar(8000),
				@nameSender varchar(25),
				@sentEcardId int

				select top 1 @customerID = customerId, @nameSender = name, @emailSender=email from customer_temp
		
				--insert vao bang sentecard
				insert into sentEcard values(@customerID,@ecardId,GETDATE(),@title,@message_ecard,@signatures)
				-- lay id cua cua thiep vua tao trong logsentmail de gui 
				select top 1 @sentEcardId =sentEcardId from sentEcard where customerId = @customerID order by sentEcardId desc 
				-- tao 1 bang contactEmail_temp
				if object_id('contactEmail_temp') is not null
				BEGIN
				  drop table contactEmail_temp
				END
				SELECT * INTO contactEmail_temp FROM contactEmail WHERE customerId  = @customerID
				WHILE(SELECT COUNT(*) FROM contactEmail_temp) > 0  
				BEGIN
					DECLARE 
					@nameReceiver varchar(25),
					@emailReceiver varchar(200),
					@birthday datetime,
					@logSentMailId int
					-- set value for available
					select top 1 @nameReceiver = name ,@emailReceiver = email, @birthday =birthday from contactEmail_temp
					-- insert vao bang listmail
					insert into listEmail values(@sentEcardId,@emailReceiver,@nameReceiver)
					-- insert vao bang logsentmail
					insert into logSentMail values(@customerID,@nameSender,@emailSender,@ecardId,GETDATE(),@title,@message_ecard,@signatures,@emailReceiver,@nameReceiver)
			
					-- lay id cua cua thiep vua tao trong logsentmail de gui 
					select top 1 @logSentMailId =LogSentMailId from logSentMail where customerId = @customerID order by LogSentMailId desc 
					-- body mail
					SET @message ='['+@emailSender+'] just sent you an ecard from Ecard.com <br/>You can view it by clicking here:<br/> <a href="'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar) +'">'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar)+' </a>';
					--SET @message ='['+@emailSender+'] just sent you an ecard from Ecard.com <br/>
					--You can view it by clicking here:<br/> <a href="http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id="'+cast(@logSentMailId as varchar) +'>'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar)+' </a>'
					Set @subject_sent = @emailSender +' sent you an ecard'
					-- sent mail
					EXEC msdb.dbo.sp_send_dbmail @profile_name='test profile',
					@recipients=@emailReceiver,
					@subject=@subject_sent,
					@body=@message ,
					@body_format = 'HTML' 
					DELETE TOP (1) PERCENT FROM contactEmail_temp 
				END
	
			update Ecard set counts = counts + 1 where ecardId = @ecardId
			DELETE TOP (1) PERCENT FROM customer_temp 
		END
END


